﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CuaHangLaptop.BIZ;

namespace CuaHangLaptop.DAL
{
    class PhieuNhap_DAL
    {
        internal static int Get_Lasted_ID()
        {
            int id = 0;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select id from PhieuNhap order by id desc", conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                id = rdr.GetInt32(0);
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return id;
        }

        // SELECT * FROM NHA SAN XUAT
        internal static List<PhieuNhap_BIZ> GetAll_PN()
        {
            List<PhieuNhap_BIZ> result = new List<PhieuNhap_BIZ>();
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from PhieuNhap", conn);
                rdr = cmd.ExecuteReader();
                if (rdr.HasRows)
                while (rdr.Read())
                {
                    PhieuNhap_BIZ dd = new PhieuNhap_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(3), rdr.GetDecimal(4), rdr.GetBoolean(5));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // LẤY NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        internal static PhieuNhap_BIZ Get_PhieuNhap_ByCode(string _maNhap)
        {
            PhieuNhap_BIZ hd1;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from PhieuNhap where MaPhieuNhap='{0}'", _maNhap);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                hd1 = new PhieuNhap_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(3), rdr.GetDecimal(4), rdr.GetBoolean(5));
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return hd1;
        }

        // LẤY TẤT CẢ NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        internal static List<PhieuNhap_BIZ> GetAll_PhieuNhap_ByNSX(string _maNSX)
        {
            List<PhieuNhap_BIZ> result = new List<PhieuNhap_BIZ>();
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("select * from PhieuNhap where MaNSX='{0}'", _maNSX);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    PhieuNhap_BIZ dd = new PhieuNhap_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(3), rdr.GetDecimal(4), rdr.GetBoolean(5));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // DELETE
        internal static void Delete(PhieuNhap_BIZ hd)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format("delete from PhieuNhap where id={0}", hd.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // UPDATE
        internal static void Update(PhieuNhap_BIZ hd)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format("update PhieuNhap set MaPhieuNhap ='{0}', MaNSX ='{1}',NgayLapPhieu= '{2}',TongTien = {3},TinhTrang= '{4}' where id={5}", hd.MaPhieuNhap, hd.Nhasx.MaNSX, hd.Ngaylap, hd.Tongtien, hd.Tinhtrang, hd.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // INSERT
        internal static int Insert(PhieuNhap_BIZ pn)
        {
            int newId = -1;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format("insert into PhieuNhap values('{0}', '{1}','{2}',{3},'{4}')", pn.MaPhieuNhap, pn.Nhasx.MaNSX, pn.Ngaylap, pn.Tongtien, pn.Tinhtrang);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                    s = String.Format("select id from PhieuNhap where MaPhieuNhap='{0}'", pn.MaPhieuNhap);
                    cmd.CommandText = s;
                    rdr = cmd.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        rdr.Read();
                        newId = Int32.Parse(rdr["id"].ToString());
                    }
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return newId;
        }
    }
}
